package org.csu.mypetstore.persistence.impl;

//import com.mysql.cj.x.protobuf.MysqlxPrepare;
import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.impl.DBUtil;
import org.csu.mypetstore.persistence.ProductDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImpl implements ProductDAO {
    private static final String getProductListByCategoryString = "select * from product where category=?";
    private static final String getProductString = "SELECT * FROM PRODUCT WHERE PRODUCTID = ?";
    //private static final String searchProductlistString = "select * from product where 1=1 ";
    private static final String searchProductListString = "select PRODUCTID,NAME,DESCN as description,CATEGORY as categoryId from PRODUCT WHERE lower(name) like ?";
    private static final String getGetProductList = "SELECT NAME FROM PRODUCT";
    private static final String getGetProductDescription = "SELECT descn FROM PRODUCT";
    @Override
    public List<Product> getProductListByCategory(String categoryId) {
        List<Product> products = new ArrayList<Product>();
//        System.out.println(categoryId+"aaaaa");

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(getProductListByCategoryString);
            pStatement.setString(1, categoryId);
            ResultSet resultSet = pStatement.executeQuery();
            while (resultSet.next()) {
                Product product = new Product();
//                product.setProductId(resultSet.getString(1));
//                product.setCategoryId(resultSet.getString(2));
//                product.setName(resultSet.getString(3));
//                product.setDescription(resultSet.getString(4));
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
//                System.out.println("aaaaa");

            }
            DBUtil.close(connection, pStatement, resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return products;
    }

    @Override
    public Product getProduct(String productId) {
        Product product = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection
                    .prepareStatement(getProductString);
            pStatement.setString(1, productId);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()) {
                product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(3));
                product.setDescription(resultSet.getString(4));
                product.setCategoryId(resultSet.getString(2));
            }
            DBUtil.close(connection, pStatement, resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return product;
    }

    @Override
    public List<Product> searchProductList(String keywords) {
        List<Product> productList = new ArrayList<>();
        try{

            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(searchProductListString);
            preparedStatement.setString(1,keywords);
            ResultSet resultSet = preparedStatement.executeQuery();


            while(resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                productList.add(product);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }

        return productList;
    }

    /*public List<Product> searchProductList(String keywords) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "select * from product where 1=1 ";
        try {
            conn = DBUtil.getConnection();
            sql += " and name like '%" + keywords + "%'";
            stm = conn.prepareStatement(sql);
//            stm.setString(1,);
            rs = stm.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setProductId(rs.getString(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                productList.add(product);
            }
            DBUtil.close(conn, stm, rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return productList;
    }*/
    @Override
    public ArrayList<String> ProductList() {
        ArrayList<String> productlist = new ArrayList();
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getGetProductList);
            ResultSet resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                String productName = resultSet.getString(1);
                productlist.add(productName);
            }
            DBUtil.close(connection, preparedStatement, resultSet);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return productlist;

    }

    @Override
    public ArrayList<String> ProductDescription() {
        ArrayList<String> productdescList = new ArrayList();
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getGetProductDescription);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                String productName = resultSet.getString(1);
                productdescList.add(productName);
            }
            DBUtil.close(connection, preparedStatement, resultSet);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return productdescList;

    }

}